package dao;

import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.Hashtable;

import orm.IndividualClass;
import orm.Student;
import orm.Teacher;
import util.DBHelper;

public class IndividualClassDAO {

	Connection conn = DBHelper.getConnection();
	Statement stmt;
	ResultSet rs;
	
	public IndividualClass getIClassID(String IClassID) throws SQLException {
		IndividualClass individualClass = new IndividualClass();
		stmt = conn.createStatement();
		rs = stmt.executeQuery("select * from individualclass where IClassID =" + IClassID + "");
		while (rs.next()) {
			int i = 1;
			individualClass.setIClassID(rs.getInt(i++));
			individualClass.setEstablishDate(rs.getString(i++));
			individualClass.setClassTerm(rs.getString(i++));
			individualClass.setTeacherID(rs.getString(i++));
			individualClass.setTeacherName(rs.getString(i++));
			individualClass.setIClassName(rs.getString(i++));
			individualClass.setAllocation(rs.getInt(i++));
			individualClass.setSpecialNeeds(rs.getString(i++));
			individualClass.setFrequency(rs.getInt(i++));
			individualClass.setIndividualTiming(rs.getInt(i++));
			
			
		}
		
		return individualClass;
	}
	
	public void insertIndividualClass(IndividualClass individualClass) throws SQLException {
		stmt = conn.createStatement();
		stmt.execute("insert into individualclass values(" + individualClass.getIClassID() + ", '" + 
				individualClass.getEstablishDate() + "', '" + individualClass.getClassTerm() + "', '" + 
				individualClass.getTeacherID() + "', '" + individualClass.getTeacherName() + "', '" + 
				individualClass.getIClassName() + "', " + individualClass.getAllocation() + ", '" +
				individualClass.getSpecialNeeds() + "', " + individualClass.getFrequency() + ", " +
				individualClass.getIndividualTiming() + ")");
		
		stmt.close();
		conn.close();
	}
	
	public void updateIndividualClass(Statement statement, IndividualClass individualClass) throws SQLException {
		statement.execute("update individualclass set IClassID =" + individualClass.getIClassID() + 
				", EstablishDate = '" + individualClass.getEstablishDate() + "', ClassTerm = '" + 
				individualClass.getClassTerm() + "', TeacherID = '" + individualClass.getTeacherID() + 
				"', TeacherName = '" + individualClass.getTeacherName() + "',IClassName = '" + 
				individualClass.getIClassName() + "', Allocation = " + individualClass.getAllocation() + 
				", SpecialNeeds = '" + individualClass.getSpecialNeeds() + 
				"', Frequency = " + individualClass.getFrequency() + ", IndividualTiming = " + 
				individualClass.getIndividualTiming() + " where IClassID = " + individualClass.getIClassID() + "");
	}
	public void updateStudent(Statement statement, Student student, IndividualClass individualClass) throws SQLException {
		statement.execute("update student set IClassName = (select IClassName from individualclass where IClassID = " + individualClass.getIClassID() + ") where IClassID = " + individualClass.getIClassID() + "");
	}
	
	public void deleteIndividualClass(Statement statement, String IClassID) throws SQLException {
		statement.execute("delete from individualclass where IClassID = " + IClassID + "");
	}
	
	public ArrayList<IndividualClass> getAllIndividualClasss() throws SQLException {
		ArrayList<IndividualClass> al = new ArrayList<IndividualClass>();
		conn = DBHelper.getConnection();
		stmt = conn.createStatement();
		rs = stmt.executeQuery("select IClassID,IClassName,TeacherName,ClassTerm, Allocation from individualclass");
		IndividualClass individualClass = null;
		
		while (rs.next()) {
			individualClass = new IndividualClass();
			individualClass.setIClassID(rs.getInt("IClassID"));
			individualClass.setIClassName(rs.getString("IClassName"));
			individualClass.setTeacherName(rs.getString("TeacherName"));
			individualClass.setClassTerm(rs.getString("ClassTerm"));
			individualClass.setAllocation(rs.getInt("Allocation"));
	
			al.add(individualClass);
		}
		
		stmt.close();
		conn.close();
		
		return al;
	}
	
	public boolean doesExist(String IClassID) throws SQLException {
		stmt = conn.createStatement();
		rs = stmt.executeQuery("select count(*) from individualclass where IClassID=" + IClassID + "");
		rs.next();
		return rs.getInt(1) == 0 ? false : true;
	}
}
